package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.bean.RecordBean;
import com.db.JdbcUtil;
import com.exception.BorrowException;
import com.exception.DBException;

public class BorrowInsDao
{
	private Connection con = null;
	private PreparedStatement ps = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	
	public void borrowRecord(RecordBean rb) throws BorrowException, DBException
	{
		String sql = "insert "
				+ "into lab_record(barcode,userID,borrowDate,sbackDate)"
				+ " values(" + "'" + rb.getBarcode() + "'" + "," + "'"
				+ rb.getUserID() + "'" + "," + "curdate()" + ","
				+ "date_add(curdate(), interval " + rb.getDays() + " day))";

		String out = "update lab_instrument set ifin='no' where barcode=" + "'"
				+ rb.getBarcode() + "'";

		con = JdbcUtil.getConnection();

		try
		{
			con.setAutoCommit(false);
			stmt= con.createStatement();
			stmt.addBatch(sql);
			stmt.addBatch(out);
			stmt.executeBatch();
			con.commit();
		}
		catch (SQLException e)
		{
			try
			{
				con.rollback();
				con.setAutoCommit(true);
			}
			catch (SQLException e1)
			{
				e1.printStackTrace();
			}
			throw new BorrowException("Fail to borrow this instrument");
		}

		finally
		{
			JdbcUtil.free(rs, stmt, con);
		}
	}

	public ArrayList<RecordBean> getBorrowResult(RecordBean recordBean) throws DBException,
			BorrowException
	{
		String sql = "select a.userID,d.barcode,a.firstname,a.lastname,a.gender,a.email,d.name,c.borrowDate,c.sbackDate from lab_user a join lab_record c on a.userID = c.userID  join lab_instrument d on d.barcode = c.barcode where a.userID=? and c.backDate is null and borrowDate=curdate()";
		con = JdbcUtil.getConnection();
		ArrayList<RecordBean> list = new ArrayList<RecordBean>();
		RecordBean  today = null;
		try
		{
			// get the user info
			ps = con.prepareStatement(sql);
			ps.setString(1, recordBean.getUserID());
			rs = ps.executeQuery();
			while (rs.next())
			{
				today = new RecordBean();
				today.setUserID(rs.getString(1));
				today.setBarcode(rs.getString(2));
				today.setFirstname(rs.getString(3));
				today.setLastname(rs.getString(4));
				today.setGender(rs.getString(5));
				today.setEmail(rs.getString(6));
				today.setInsName(rs.getString(7));
				today.setBorrowDate(rs.getDate(8));
				today.setSBackDate(rs.getDate(9));
				list.add(today);
			}
		}
		catch (SQLException e)
		{
			throw new BorrowException("Fail to get result of borrow infomation");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return list;
	}
}
